{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "helper load finish!!!\n"
     ]
    }
   ],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "from base_helper import *\n",
    "test_op_rd1 = get_operation_round1_new()\n",
    "train_op_tr = get_operation_train_new()\n",
    "train_tst = get_transaction_train_new()\n",
    "test_op_rd1 = get_transaction_round1_new()\n",
    "\n",
    "tag = get_tag_train_new()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# train_op_trs = pd.merge(train_op_tr, tag, on='UID', how='left')\n",
    "train_tsts =  pd.merge(train_tst, tag, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The shape is :(264654, 28)\n"
     ]
    }
   ],
   "source": [
    "print(\"The shape is :{}\".format(train_tsts.shape))\n",
    "pd.set_option('max_colwidth', 100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(30542, 1)\n"
     ]
    }
   ],
   "source": [
    "train_tst_feature = pd.DataFrame({'UID':[i for i in train_tsts.UID.unique()]})\n",
    "print(train_tst_feature.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易天的个数统计,平均每天的操作次数，每个时间段的操作次数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>channel</th>\n",
       "      <th>day</th>\n",
       "      <th>time</th>\n",
       "      <th>trans_amt</th>\n",
       "      <th>amt_src1</th>\n",
       "      <th>merchant</th>\n",
       "      <th>code1</th>\n",
       "      <th>code2</th>\n",
       "      <th>trans_type1</th>\n",
       "      <th>...</th>\n",
       "      <th>bal</th>\n",
       "      <th>amt_src2</th>\n",
       "      <th>acc_id2</th>\n",
       "      <th>acc_id3</th>\n",
       "      <th>geo_code</th>\n",
       "      <th>trans_type2</th>\n",
       "      <th>market_code</th>\n",
       "      <th>market_type</th>\n",
       "      <th>ip1_sub</th>\n",
       "      <th>Tag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>90698</th>\n",
       "      <td>10000</td>\n",
       "      <td>140</td>\n",
       "      <td>26</td>\n",
       "      <td>12:23:56</td>\n",
       "      <td>5536</td>\n",
       "      <td>f29829bc82459191</td>\n",
       "      <td>88aa547576f43f85</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>9a8ee16bde15e38a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1e3ea9498c461cbf</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134052</th>\n",
       "      <td>10000</td>\n",
       "      <td>140</td>\n",
       "      <td>26</td>\n",
       "      <td>12:24:17</td>\n",
       "      <td>5536</td>\n",
       "      <td>f29829bc82459191</td>\n",
       "      <td>88aa547576f43f85</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>9a8ee16bde15e38a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1e3ea9498c461cbf</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          UID  channel  day      time  trans_amt          amt_src1  \\\n",
       "90698   10000      140   26  12:23:56       5536  f29829bc82459191   \n",
       "134052  10000      140   26  12:24:17       5536  f29829bc82459191   \n",
       "\n",
       "                merchant code1 code2       trans_type1 ...  bal  \\\n",
       "90698   88aa547576f43f85   NaN   NaN  c2f2023d279665b2 ...  100   \n",
       "134052  88aa547576f43f85   NaN   NaN  c2f2023d279665b2 ...  100   \n",
       "\n",
       "                amt_src2 acc_id2 acc_id3 geo_code trans_type2 market_code  \\\n",
       "90698   9a8ee16bde15e38a     NaN     NaN      NaN       105.0         NaN   \n",
       "134052  9a8ee16bde15e38a     NaN     NaN      NaN       105.0         NaN   \n",
       "\n",
       "       market_type           ip1_sub Tag  \n",
       "90698          NaN  1e3ea9498c461cbf   1  \n",
       "134052         NaN  1e3ea9498c461cbf   1  \n",
       "\n",
       "[2 rows x 28 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_tsts[train_tsts.UID == 10000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_tst_day = train_tsts.copy()\n",
    "\n",
    "'''每个用户的总共交易次数，以及每个用户平均每天的交易次数'''\n",
    "train_tst_feature = pd.merge(train_tst_feature, \n",
    "                        train_tst_day.groupby('UID', as_index=False)['day'].agg({'trans_count':'count'}))\n",
    "train_tst_day.drop_duplicates(['UID', 'day'], keep='first', inplace=True)\n",
    "train_tst_day = train_tst_day.groupby('UID', as_index=False)['day'].agg({'day_count':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_tst_day, on='UID', how='left')\n",
    "\n",
    "train_tst_feature['avg_day_trans'] = train_tst_feature.trans_count / train_tst_feature.day_count\n",
    "# train_tst_feature.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''统计每个不同时间段的交易次数，以及每个用户每个时间段的平均交易次数'''\n",
    "train_time = train_tsts.copy()\n",
    "train_time.time = train_time.time.str[:2]\n",
    "\n",
    "train_time.time = train_time.time.astype('int')\n",
    "\n",
    "def time_help(data):\n",
    "    if (data > 7) & (data <= 12):\n",
    "        return 0\n",
    "    elif (data > 12) & (data <= 19):\n",
    "        return 1\n",
    "    elif (data > 19) & (data <= 24):\n",
    "        return 2\n",
    "    elif (data >= 0) & (data <= 7):\n",
    "        return 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_time.time = train_time.time.map(time_help)\n",
    "train_time = pd.concat([train_time.UID, pd.get_dummies(train_time.time, prefix='time')], axis=1)\n",
    "train_time = train_time.groupby('UID', as_index=False).sum()\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_time, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "time_func = lambda x: 0 if x == np.inf else x\n",
    "train_tst_feature['avg_time0_trans'] = (train_tst_feature.trans_count / train_tst_feature.time_0).map(time_func)\n",
    "train_tst_feature['avg_time1_trans'] = (train_tst_feature.trans_count / train_tst_feature.time_1).map(time_func)\n",
    "train_tst_feature['avg_time2_trans'] = (train_tst_feature.trans_count / train_tst_feature.time_2).map(time_func)\n",
    "train_tst_feature['avg_time3_trans'] = (train_tst_feature.trans_count / train_tst_feature.time_3).map(time_func)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>trans_count</th>\n",
       "      <th>day_count</th>\n",
       "      <th>avg_day_trans</th>\n",
       "      <th>time_0</th>\n",
       "      <th>time_1</th>\n",
       "      <th>time_2</th>\n",
       "      <th>time_3</th>\n",
       "      <th>avg_time0_trans</th>\n",
       "      <th>avg_time1_trans</th>\n",
       "      <th>avg_time2_trans</th>\n",
       "      <th>avg_time3_trans</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19092</td>\n",
       "      <td>19</td>\n",
       "      <td>9</td>\n",
       "      <td>2.111111</td>\n",
       "      <td>5.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.800000</td>\n",
       "      <td>2.111111</td>\n",
       "      <td>9.5</td>\n",
       "      <td>6.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13465</td>\n",
       "      <td>19</td>\n",
       "      <td>8</td>\n",
       "      <td>2.375000</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>2.375000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13713</td>\n",
       "      <td>485</td>\n",
       "      <td>17</td>\n",
       "      <td>28.529412</td>\n",
       "      <td>172.0</td>\n",
       "      <td>303.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.819767</td>\n",
       "      <td>1.600660</td>\n",
       "      <td>48.5</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22703</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17816</td>\n",
       "      <td>14</td>\n",
       "      <td>9</td>\n",
       "      <td>1.555556</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.500000</td>\n",
       "      <td>1.400000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     UID  trans_count  day_count  avg_day_trans  time_0  time_1  time_2  \\\n",
       "0  19092           19          9       2.111111     5.0     9.0     2.0   \n",
       "1  13465           19          8       2.375000     7.0     8.0     0.0   \n",
       "2  13713          485         17      28.529412   172.0   303.0    10.0   \n",
       "3  22703            6          2       3.000000     0.0     2.0     2.0   \n",
       "4  17816           14          9       1.555556     4.0    10.0     0.0   \n",
       "\n",
       "   time_3  avg_time0_trans  avg_time1_trans  avg_time2_trans  avg_time3_trans  \n",
       "0     3.0         3.800000         2.111111              9.5         6.333333  \n",
       "1     4.0         2.714286         2.375000              0.0         4.750000  \n",
       "2     0.0         2.819767         1.600660             48.5         0.000000  \n",
       "3     2.0         0.000000         3.000000              3.0         3.000000  \n",
       "4     0.0         3.500000         1.400000              0.0         0.000000  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_tst_feature.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 取每个用户的相邻两次交易天数差值的平均值，最大值，最大值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_time_sub = train_tsts.copy()\n",
    "train_time_sub.sort_values(by=['UID','day','time'],inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_time_sub['day_shift'] = train_time_sub.groupby('UID')['day'].shift(-1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_time_sub = train_time_sub[['UID', 'day', 'time', 'day_shift']]\n",
    "train_time_sub['sub'] = train_time_sub['day_shift'] - train_time_sub['day']\n",
    "train_time_sub['sub'] = train_time_sub['sub'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_time_sub = train_time_sub.groupby('UID', as_index=False)['sub'].agg({'mean','max','min'}).add_prefix('day_shift_')\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_time_sub, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易平台的个数，以及每个平台的交易金额的大小trans_amt的统计信息值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>channel</th>\n",
       "      <th>day</th>\n",
       "      <th>time</th>\n",
       "      <th>trans_amt</th>\n",
       "      <th>amt_src1</th>\n",
       "      <th>merchant</th>\n",
       "      <th>code1</th>\n",
       "      <th>code2</th>\n",
       "      <th>trans_type1</th>\n",
       "      <th>...</th>\n",
       "      <th>bal</th>\n",
       "      <th>amt_src2</th>\n",
       "      <th>acc_id2</th>\n",
       "      <th>acc_id3</th>\n",
       "      <th>geo_code</th>\n",
       "      <th>trans_type2</th>\n",
       "      <th>market_code</th>\n",
       "      <th>market_type</th>\n",
       "      <th>ip1_sub</th>\n",
       "      <th>Tag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [UID, channel, day, time, trans_amt, amt_src1, merchant, code1, code2, trans_type1, acc_id1, device_code1, device_code2, device_code3, device1, device2, mac1, ip1, bal, amt_src2, acc_id2, acc_id3, geo_code, trans_type2, market_code, market_type, ip1_sub, Tag]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 28 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_tsts[train_tsts.channel.isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>channel</th>\n",
       "      <th>day</th>\n",
       "      <th>time</th>\n",
       "      <th>trans_amt</th>\n",
       "      <th>amt_src1</th>\n",
       "      <th>merchant</th>\n",
       "      <th>code1</th>\n",
       "      <th>code2</th>\n",
       "      <th>trans_type1</th>\n",
       "      <th>...</th>\n",
       "      <th>bal</th>\n",
       "      <th>amt_src2</th>\n",
       "      <th>acc_id2</th>\n",
       "      <th>acc_id3</th>\n",
       "      <th>geo_code</th>\n",
       "      <th>trans_type2</th>\n",
       "      <th>market_code</th>\n",
       "      <th>market_type</th>\n",
       "      <th>ip1_sub</th>\n",
       "      <th>Tag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>21031</th>\n",
       "      <td>10002</td>\n",
       "      <td>140</td>\n",
       "      <td>29</td>\n",
       "      <td>09:56:10</td>\n",
       "      <td>2818</td>\n",
       "      <td>4d7831c6f695ab19</td>\n",
       "      <td>5b9283139b4920cc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>wmgu</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4dc6f70900fadcab</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36860</th>\n",
       "      <td>10002</td>\n",
       "      <td>102</td>\n",
       "      <td>29</td>\n",
       "      <td>09:55:57</td>\n",
       "      <td>2818</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>8f57527418b3f457</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>61bfb66c928f36ac</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>cf6e3a074407c379</td>\n",
       "      <td>cbf3049442f24f31</td>\n",
       "      <td>864bf3b209c301e0</td>\n",
       "      <td>wmgu</td>\n",
       "      <td>102.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4dc6f70900fadcab</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113233</th>\n",
       "      <td>10002</td>\n",
       "      <td>140</td>\n",
       "      <td>29</td>\n",
       "      <td>09:56:37</td>\n",
       "      <td>1459</td>\n",
       "      <td>c5fc631370cabc0d</td>\n",
       "      <td>5b9283139b4920cc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>wmgu</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4dc6f70900fadcab</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          UID  channel  day      time  trans_amt          amt_src1  \\\n",
       "21031   10002      140   29  09:56:10       2818  4d7831c6f695ab19   \n",
       "36860   10002      102   29  09:55:57       2818  155c9e1c32bd0fa2   \n",
       "113233  10002      140   29  09:56:37       1459  c5fc631370cabc0d   \n",
       "\n",
       "                merchant code1 code2       trans_type1 ...  bal  \\\n",
       "21031   5b9283139b4920cc   NaN   NaN  c2f2023d279665b2 ...  100   \n",
       "36860   8f57527418b3f457   NaN   NaN  61bfb66c928f36ac ...  100   \n",
       "113233  5b9283139b4920cc   NaN   NaN  c2f2023d279665b2 ...  100   \n",
       "\n",
       "                amt_src2           acc_id2           acc_id3 geo_code  \\\n",
       "21031                NaN               NaN               NaN     wmgu   \n",
       "36860   cf6e3a074407c379  cbf3049442f24f31  864bf3b209c301e0     wmgu   \n",
       "113233               NaN               NaN               NaN     wmgu   \n",
       "\n",
       "       trans_type2 market_code market_type           ip1_sub Tag  \n",
       "21031        105.0         NaN         NaN  4dc6f70900fadcab   0  \n",
       "36860        102.0         NaN         NaN  4dc6f70900fadcab   0  \n",
       "113233       105.0         NaN         NaN  4dc6f70900fadcab   0  \n",
       "\n",
       "[3 rows x 28 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_tsts[train_tsts.UID == 10002]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''交易平台的个数以及平均每天使用不同平台的个数'''\n",
    "train_channel_count = train_tsts.copy()\n",
    "train_channel_count.drop_duplicates(['UID', 'channel'], inplace=True)\n",
    "train_channel_count = train_channel_count.groupby('UID', as_index=False)['channel'].agg({'channel_count':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_channel_count, on='UID', how='left')\n",
    "train_tst_feature['avg_day_diff_channel'] = train_tst_feature['channel_count'] / train_tst_feature['day_count']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''平均每天使用的平台个数'''\n",
    "train_avg_channel = train_tsts.copy()\n",
    "train_avg_channel.drop_duplicates(['UID', 'day', 'channel'], inplace=True)\n",
    "train_avg_channel = train_avg_channel.groupby('UID', as_index=False)['channel'].agg({'channel_day_count':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_avg_channel, on='UID', how='left')\n",
    "train_tst_feature['avg_day_channel'] = train_tst_feature['channel_day_count'] / train_tst_feature['day_count']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''每个平台的交易金额的统计信息值'''\n",
    "train_channel_amt = train_tsts.copy()\n",
    "train_channel_amt = train_channel_amt.groupby(['UID','channel'])['trans_amt'].agg({'mean','max','min'}).add_prefix('amt_channel_').unstack('channel')\n",
    "train_channel_amt.columns = [x[0]+\"_\"+str(x[1]) for x in train_channel_amt.columns.ravel()]\n",
    "train_channel_amt.fillna(0, inplace=True)\n",
    "train_channel_amt.reset_index(inplace=True)\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_channel_amt, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 营销活动的相关特征，每天参与营销活动的类型，一共参与的营销活动，每个营销类型的金额相关特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''平均每天参与的营销活动次数'''\n",
    "train_market_code = train_tsts.copy()\n",
    "train_market_code['market_code'].fillna(0, inplace=True)\n",
    "train_market_code.drop_duplicates(['UID','day', 'market_code'], inplace=True)\n",
    "train_market_code = train_market_code.groupby('UID', as_index=False)['market_code'].agg({'mark_code_count':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_market_code, on='UID', how='left')\n",
    "train_tst_feature['avg_day_mark_code'] = train_tst_feature['mark_code_count'] / train_tst_feature['day_count']\n",
    "\n",
    "'''平均每天参与不同营销活动的次数'''\n",
    "train_market_code_diff = train_tsts.copy()\n",
    "train_market_code_diff['market_code'].fillna(0, inplace=True)\n",
    "train_market_code_diff.drop_duplicates(['UID', 'market_code'], inplace=True)\n",
    "train_market_code_diff = train_market_code_diff.groupby('UID', as_index=False)['market_code'].agg({'mark_code_count_diff':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_market_code_diff, on='UID', how='left')\n",
    "train_tst_feature['avg_day_diff_mark_code'] = train_tst_feature['mark_code_count_diff'] / train_tst_feature['day_count']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "'''不同的营销类型的金额统计信息'''\n",
    "train_market_type = train_tsts.copy()\n",
    "train_market_type['market_type'].fillna(0, inplace=True)\n",
    "train_market_type = train_market_type.groupby(['UID','market_type'])['trans_amt'].agg({'mean','max','min'}).add_prefix('market_type_').unstack()\n",
    "train_market_type.columns = [x[0]+\"_\"+str(x[1]) for x in train_market_type.columns.ravel()]\n",
    "train_market_type.fillna(0, inplace=True)\n",
    "train_market_type.reset_index(inplace=True)\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_market_type, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易类型的统计分析以及和交易金额的大小之间的关系信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['26bcf43a19df14c8' 'c2f2023d279665b2' '6d55c54c8b1056fb'\n",
      " '61bfb66c928f36ac' 'e0d7b8768da99dd4' 'd9c417304a5ae70c'\n",
      " '9d7dd7b80e806024' '4adc3de71fe1a83c' 'a19e7a8951e54c06'\n",
      " 'ced62357ad496957' 'eb8d10591677bbe1' 'e903cf2a79b83d37'\n",
      " '85bced5214d33ad2' 'fd4d2d1006a95637' '3f469aa3836e71cb'] 15\n"
     ]
    }
   ],
   "source": [
    "print(train_tsts['trans_type1'].unique(), len(train_tsts['trans_type1'].unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''平均每天交易类型1,2的次数'''\n",
    "train_type_1 = train_tsts.copy()\n",
    "train_type_1.drop_duplicates(['UID', 'day', 'trans_type1'], inplace=True)\n",
    "train_type_1 = train_type_1.groupby('UID', as_index=False)['trans_type1'].agg({'trans_type1_count':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_type_1, on='UID', how='left')\n",
    "train_tst_feature['avg_day_trans_type1'] = train_tst_feature['trans_type1_count'] / train_tst_feature['day_count']\n",
    "\n",
    "\n",
    "train_type_2 = train_tsts.copy()\n",
    "train_type_2.drop_duplicates(['UID', 'day', 'trans_type2'], inplace=True)\n",
    "train_type_2 = train_type_2.groupby('UID', as_index=False)['trans_type2'].agg({'trans_type2_count':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_type_2, on='UID', how='left')\n",
    "train_tst_feature['avg_day_trans_type2'] = train_tst_feature['trans_type2_count'] / train_tst_feature['day_count']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''每个用户的交类型2的金额统计信息'''\n",
    "train_type2_amt = train_tsts.copy()\n",
    "train_type2_amt['trans_type2'].fillna(0, inplace=True)\n",
    "train_type2_amt = train_type2_amt.groupby(['UID','trans_type2'])['trans_amt'].agg({'mean','max','min'}).add_prefix('trans_type2_').unstack()\n",
    "train_type2_amt.columns = [x[0]+\"_\"+str(int(x[1])) for x in train_type2_amt.columns.ravel()]\n",
    "train_type2_amt.fillna(0, inplace=True)\n",
    "train_type2_amt.reset_index(inplace=True)\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_type2_amt, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易金额和余额的统计信息trans_amt, bal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>channel</th>\n",
       "      <th>day</th>\n",
       "      <th>time</th>\n",
       "      <th>trans_amt</th>\n",
       "      <th>amt_src1</th>\n",
       "      <th>merchant</th>\n",
       "      <th>code1</th>\n",
       "      <th>code2</th>\n",
       "      <th>trans_type1</th>\n",
       "      <th>...</th>\n",
       "      <th>bal</th>\n",
       "      <th>amt_src2</th>\n",
       "      <th>acc_id2</th>\n",
       "      <th>acc_id3</th>\n",
       "      <th>geo_code</th>\n",
       "      <th>trans_type2</th>\n",
       "      <th>market_code</th>\n",
       "      <th>market_type</th>\n",
       "      <th>ip1_sub</th>\n",
       "      <th>Tag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [UID, channel, day, time, trans_amt, amt_src1, merchant, code1, code2, trans_type1, acc_id1, device_code1, device_code2, device_code3, device1, device2, mac1, ip1, bal, amt_src2, acc_id2, acc_id3, geo_code, trans_type2, market_code, market_type, ip1_sub, Tag]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 28 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_tsts[train_tsts.bal.isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>trans_count</th>\n",
       "      <th>day_count</th>\n",
       "      <th>avg_day_trans</th>\n",
       "      <th>time_0</th>\n",
       "      <th>time_1</th>\n",
       "      <th>time_2</th>\n",
       "      <th>time_3</th>\n",
       "      <th>avg_time0_trans</th>\n",
       "      <th>avg_time1_trans</th>\n",
       "      <th>...</th>\n",
       "      <th>trans_type2_mean_0</th>\n",
       "      <th>trans_type2_mean_102</th>\n",
       "      <th>trans_type2_mean_103</th>\n",
       "      <th>trans_type2_mean_104</th>\n",
       "      <th>trans_type2_mean_105</th>\n",
       "      <th>trans_type2_max_0</th>\n",
       "      <th>trans_type2_max_102</th>\n",
       "      <th>trans_type2_max_103</th>\n",
       "      <th>trans_type2_max_104</th>\n",
       "      <th>trans_type2_max_105</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19092</td>\n",
       "      <td>19</td>\n",
       "      <td>9</td>\n",
       "      <td>2.111111</td>\n",
       "      <td>5.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.800000</td>\n",
       "      <td>2.111111</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>12223.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4538.357143</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27282.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30626.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13465</td>\n",
       "      <td>19</td>\n",
       "      <td>8</td>\n",
       "      <td>2.375000</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>2.375000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>12331.750000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1527.666667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27282.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3497.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13713</td>\n",
       "      <td>485</td>\n",
       "      <td>17</td>\n",
       "      <td>28.529412</td>\n",
       "      <td>172.0</td>\n",
       "      <td>303.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.819767</td>\n",
       "      <td>1.600660</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>8529.818182</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>780.937209</td>\n",
       "      <td>0.0</td>\n",
       "      <td>228435.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2818.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22703</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>5.197350e+04</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>112699.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17816</td>\n",
       "      <td>14</td>\n",
       "      <td>9</td>\n",
       "      <td>1.555556</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.500000</td>\n",
       "      <td>1.400000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>3429.500000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3198.500000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4177.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4449.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>12037</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>4.500000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.500000</td>\n",
       "      <td>...</td>\n",
       "      <td>6.672400e+03</td>\n",
       "      <td>9069.750000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>21302.0</td>\n",
       "      <td>22389.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>18983</td>\n",
       "      <td>33</td>\n",
       "      <td>14</td>\n",
       "      <td>2.357143</td>\n",
       "      <td>10.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.300000</td>\n",
       "      <td>2.357143</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>316727.200000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>69381.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>920273.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>135470.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>12741</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2.500000</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1431.400000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1730.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>15986</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>8.179500e+03</td>\n",
       "      <td>12182.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>24020.0</td>\n",
       "      <td>24020.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10057</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>2.333333</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.333333</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>2236.500000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>13691.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8798.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13691.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>18023</td>\n",
       "      <td>18</td>\n",
       "      <td>7</td>\n",
       "      <td>2.571429</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.571429</td>\n",
       "      <td>2.250000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>81648.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2359.250000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>81648.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5957.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>10953</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>3769.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3293.500000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3769.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3769.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>20331</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>8254.500000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3395.250000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13691.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9021.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>15008</td>\n",
       "      <td>42</td>\n",
       "      <td>18</td>\n",
       "      <td>2.333333</td>\n",
       "      <td>10.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.200000</td>\n",
       "      <td>2.333333</td>\n",
       "      <td>...</td>\n",
       "      <td>1.459000e+03</td>\n",
       "      <td>267336.950000</td>\n",
       "      <td>3490.750000</td>\n",
       "      <td>1459.000000</td>\n",
       "      <td>2259.000000</td>\n",
       "      <td>1459.0</td>\n",
       "      <td>1359240.0</td>\n",
       "      <td>10212.0</td>\n",
       "      <td>1459.0</td>\n",
       "      <td>5944.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>12983</td>\n",
       "      <td>22</td>\n",
       "      <td>6</td>\n",
       "      <td>3.666667</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>3.666667</td>\n",
       "      <td>22.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1472.333333</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>13691.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2818.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13691.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>21206</td>\n",
       "      <td>31</td>\n",
       "      <td>10</td>\n",
       "      <td>3.100000</td>\n",
       "      <td>6.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.166667</td>\n",
       "      <td>3.100000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>6845.380952</td>\n",
       "      <td>3941.750000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4177.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27282.0</td>\n",
       "      <td>6080.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8078.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16882</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>54465.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4947.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>54465.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>11298</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1730.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1594.500000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1730.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1730.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>14873</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>2.250000</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>3788.857143</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6895.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13691.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10728.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>21509</td>\n",
       "      <td>102</td>\n",
       "      <td>24</td>\n",
       "      <td>4.250000</td>\n",
       "      <td>39.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.615385</td>\n",
       "      <td>2.833333</td>\n",
       "      <td>...</td>\n",
       "      <td>1.021160e+04</td>\n",
       "      <td>182532.500000</td>\n",
       "      <td>2124.018519</td>\n",
       "      <td>6672.480000</td>\n",
       "      <td>2138.250000</td>\n",
       "      <td>37068.0</td>\n",
       "      <td>1359240.0</td>\n",
       "      <td>14884.0</td>\n",
       "      <td>38645.0</td>\n",
       "      <td>4280.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>17520</td>\n",
       "      <td>4030</td>\n",
       "      <td>11</td>\n",
       "      <td>366.363636</td>\n",
       "      <td>837.0</td>\n",
       "      <td>1949.0</td>\n",
       "      <td>1239.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.814815</td>\n",
       "      <td>2.067727</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>24510.142857</td>\n",
       "      <td>1141.333333</td>\n",
       "      <td>107.599284</td>\n",
       "      <td>185.900000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>80759.0</td>\n",
       "      <td>2818.0</td>\n",
       "      <td>113.0</td>\n",
       "      <td>2818.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>17259</td>\n",
       "      <td>70</td>\n",
       "      <td>20</td>\n",
       "      <td>3.500000</td>\n",
       "      <td>52.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.346154</td>\n",
       "      <td>4.666667</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>675875.770833</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>917519.375000</td>\n",
       "      <td>77959.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5436663.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1359240.0</td>\n",
       "      <td>136014.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>13426</td>\n",
       "      <td>105</td>\n",
       "      <td>8</td>\n",
       "      <td>13.125000</td>\n",
       "      <td>49.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.142857</td>\n",
       "      <td>2.100000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1692.489362</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1335.090909</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3361.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1730.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>16146</td>\n",
       "      <td>19</td>\n",
       "      <td>4</td>\n",
       "      <td>4.750000</td>\n",
       "      <td>6.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.166667</td>\n",
       "      <td>1.900000</td>\n",
       "      <td>...</td>\n",
       "      <td>1.459000e+03</td>\n",
       "      <td>8267.400000</td>\n",
       "      <td>1812.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1096.333333</td>\n",
       "      <td>1459.0</td>\n",
       "      <td>26720.0</td>\n",
       "      <td>2818.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1459.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>17856</td>\n",
       "      <td>22</td>\n",
       "      <td>4</td>\n",
       "      <td>5.500000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.222222</td>\n",
       "      <td>...</td>\n",
       "      <td>1.020233e+04</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1911.777778</td>\n",
       "      <td>3578.900000</td>\n",
       "      <td>24931.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5536.0</td>\n",
       "      <td>4802.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>20280</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>3.562850e+04</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>77842.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>21856</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>2.200000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>1.833333</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>10099.250000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>8163.666667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>56169.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18312.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>11103</td>\n",
       "      <td>40</td>\n",
       "      <td>10</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>30.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.333333</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>8.160110e+05</td>\n",
       "      <td>62734.954545</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5053.545455</td>\n",
       "      <td>3724.000000</td>\n",
       "      <td>1088266.0</td>\n",
       "      <td>566622.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38155.0</td>\n",
       "      <td>5536.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>15283</td>\n",
       "      <td>53</td>\n",
       "      <td>16</td>\n",
       "      <td>3.312500</td>\n",
       "      <td>27.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.962963</td>\n",
       "      <td>2.409091</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>62270.738095</td>\n",
       "      <td>2566.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3905.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1359240.0</td>\n",
       "      <td>5737.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7893.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>11981</td>\n",
       "      <td>21</td>\n",
       "      <td>6</td>\n",
       "      <td>3.500000</td>\n",
       "      <td>5.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.200000</td>\n",
       "      <td>2.333333</td>\n",
       "      <td>...</td>\n",
       "      <td>1.137073e+06</td>\n",
       "      <td>819127.875000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2718381.0</td>\n",
       "      <td>1631069.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30512</th>\n",
       "      <td>58228</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>4.992000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4992.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30513</th>\n",
       "      <td>59636</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2818.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2818.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30514</th>\n",
       "      <td>53075</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6895.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6895.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30515</th>\n",
       "      <td>50983</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>7.847000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>7847.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30516</th>\n",
       "      <td>63285</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>2.682000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2682.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30517</th>\n",
       "      <td>64532</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>7.684000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>7684.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30518</th>\n",
       "      <td>63456</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>4.449000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4449.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30519</th>\n",
       "      <td>50664</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>4.992000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4992.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30520</th>\n",
       "      <td>54307</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>4.992000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4992.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30521</th>\n",
       "      <td>61058</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>2.682000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2682.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30522</th>\n",
       "      <td>50464</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4449.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4449.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30523</th>\n",
       "      <td>64044</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2818.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2818.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30524</th>\n",
       "      <td>67622</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>10973.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10973.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30525</th>\n",
       "      <td>60046</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2818.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2818.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30526</th>\n",
       "      <td>57366</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>15050.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15050.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30527</th>\n",
       "      <td>66473</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>4.992000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4992.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30528</th>\n",
       "      <td>67315</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2818.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2818.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30529</th>\n",
       "      <td>54931</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2818.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2818.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30530</th>\n",
       "      <td>50151</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>4.449000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4449.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30531</th>\n",
       "      <td>67635</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>2.671000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2671.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30532</th>\n",
       "      <td>61852</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>271928.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>271928.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30533</th>\n",
       "      <td>50221</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>2.671000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2671.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30534</th>\n",
       "      <td>64330</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>13691.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13691.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30535</th>\n",
       "      <td>53025</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>2.682000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2682.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30536</th>\n",
       "      <td>53733</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2818.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2818.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30537</th>\n",
       "      <td>61409</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>5.242000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5242.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30538</th>\n",
       "      <td>54248</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1268.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1268.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30539</th>\n",
       "      <td>53801</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>2.682000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2682.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30540</th>\n",
       "      <td>51873</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>4.992000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4992.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30541</th>\n",
       "      <td>61489</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>1.397400e+04</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>13974.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>30542 rows × 66 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         UID  trans_count  day_count  avg_day_trans  time_0  time_1  time_2  \\\n",
       "0      19092           19          9       2.111111     5.0     9.0     2.0   \n",
       "1      13465           19          8       2.375000     7.0     8.0     0.0   \n",
       "2      13713          485         17      28.529412   172.0   303.0    10.0   \n",
       "3      22703            6          2       3.000000     0.0     2.0     2.0   \n",
       "4      17816           14          9       1.555556     4.0    10.0     0.0   \n",
       "5      12037            9          2       4.500000     0.0     6.0     0.0   \n",
       "6      18983           33         14       2.357143    10.0    14.0     9.0   \n",
       "7      12741            5          2       2.500000     5.0     0.0     0.0   \n",
       "8      15986            6          2       3.000000     0.0     0.0     3.0   \n",
       "9      10057            7          3       2.333333     0.0     3.0     4.0   \n",
       "10     18023           18          7       2.571429     7.0     8.0     3.0   \n",
       "11     10953            3          1       3.000000     0.0     3.0     0.0   \n",
       "12     20331            6          2       3.000000     0.0     6.0     0.0   \n",
       "13     15008           42         18       2.333333    10.0    18.0    14.0   \n",
       "14     12983           22          6       3.666667     6.0     1.0     0.0   \n",
       "15     21206           31         10       3.100000     6.0    10.0    12.0   \n",
       "16     16882            4          1       4.000000     4.0     0.0     0.0   \n",
       "17     11298           15          5       3.000000    15.0     0.0     0.0   \n",
       "18     14873            9          4       2.250000     9.0     0.0     0.0   \n",
       "19     21509          102         24       4.250000    39.0    36.0    27.0   \n",
       "20     17520         4030         11     366.363636   837.0  1949.0  1239.0   \n",
       "21     17259           70         20       3.500000    52.0    15.0     3.0   \n",
       "22     13426          105          8      13.125000    49.0    50.0     0.0   \n",
       "23     16146           19          4       4.750000     6.0    10.0     3.0   \n",
       "24     17856           22          4       5.500000     0.0    18.0     4.0   \n",
       "25     20280            6          2       3.000000     2.0     2.0     2.0   \n",
       "26     21856           11          5       2.200000     1.0     6.0     1.0   \n",
       "27     11103           40         10       4.000000    30.0     2.0     3.0   \n",
       "28     15283           53         16       3.312500    27.0    22.0     0.0   \n",
       "29     11981           21          6       3.500000     5.0     9.0     5.0   \n",
       "...      ...          ...        ...            ...     ...     ...     ...   \n",
       "30512  58228            1          1       1.000000     0.0     0.0     1.0   \n",
       "30513  59636            1          1       1.000000     1.0     0.0     0.0   \n",
       "30514  53075            1          1       1.000000     0.0     0.0     1.0   \n",
       "30515  50983            1          1       1.000000     1.0     0.0     0.0   \n",
       "30516  63285            1          1       1.000000     0.0     0.0     1.0   \n",
       "30517  64532            1          1       1.000000     0.0     0.0     0.0   \n",
       "30518  63456            1          1       1.000000     0.0     0.0     1.0   \n",
       "30519  50664            1          1       1.000000     0.0     1.0     0.0   \n",
       "30520  54307            1          1       1.000000     0.0     0.0     1.0   \n",
       "30521  61058            1          1       1.000000     1.0     0.0     0.0   \n",
       "30522  50464            1          1       1.000000     0.0     1.0     0.0   \n",
       "30523  64044            1          1       1.000000     0.0     1.0     0.0   \n",
       "30524  67622            1          1       1.000000     0.0     1.0     0.0   \n",
       "30525  60046            1          1       1.000000     0.0     1.0     0.0   \n",
       "30526  57366            1          1       1.000000     0.0     1.0     0.0   \n",
       "30527  66473            1          1       1.000000     0.0     1.0     0.0   \n",
       "30528  67315            1          1       1.000000     0.0     1.0     0.0   \n",
       "30529  54931            1          1       1.000000     0.0     1.0     0.0   \n",
       "30530  50151            1          1       1.000000     0.0     0.0     0.0   \n",
       "30531  67635            1          1       1.000000     1.0     0.0     0.0   \n",
       "30532  61852            1          1       1.000000     1.0     0.0     0.0   \n",
       "30533  50221            1          1       1.000000     0.0     0.0     1.0   \n",
       "30534  64330            1          1       1.000000     1.0     0.0     0.0   \n",
       "30535  53025            1          1       1.000000     1.0     0.0     0.0   \n",
       "30536  53733            1          1       1.000000     0.0     1.0     0.0   \n",
       "30537  61409            1          1       1.000000     1.0     0.0     0.0   \n",
       "30538  54248            1          1       1.000000     1.0     0.0     0.0   \n",
       "30539  53801            1          1       1.000000     1.0     0.0     0.0   \n",
       "30540  51873            1          1       1.000000     1.0     0.0     0.0   \n",
       "30541  61489            1          1       1.000000     1.0     0.0     0.0   \n",
       "\n",
       "       time_3  avg_time0_trans  avg_time1_trans         ...           \\\n",
       "0         3.0         3.800000         2.111111         ...            \n",
       "1         4.0         2.714286         2.375000         ...            \n",
       "2         0.0         2.819767         1.600660         ...            \n",
       "3         2.0         0.000000         3.000000         ...            \n",
       "4         0.0         3.500000         1.400000         ...            \n",
       "5         3.0         0.000000         1.500000         ...            \n",
       "6         0.0         3.300000         2.357143         ...            \n",
       "7         0.0         1.000000         0.000000         ...            \n",
       "8         3.0         0.000000         0.000000         ...            \n",
       "9         0.0         0.000000         2.333333         ...            \n",
       "10        0.0         2.571429         2.250000         ...            \n",
       "11        0.0         0.000000         1.000000         ...            \n",
       "12        0.0         0.000000         1.000000         ...            \n",
       "13        0.0         4.200000         2.333333         ...            \n",
       "14       15.0         3.666667        22.000000         ...            \n",
       "15        3.0         5.166667         3.100000         ...            \n",
       "16        0.0         1.000000         0.000000         ...            \n",
       "17        0.0         1.000000         0.000000         ...            \n",
       "18        0.0         1.000000         0.000000         ...            \n",
       "19        0.0         2.615385         2.833333         ...            \n",
       "20        5.0         4.814815         2.067727         ...            \n",
       "21        0.0         1.346154         4.666667         ...            \n",
       "22        6.0         2.142857         2.100000         ...            \n",
       "23        0.0         3.166667         1.900000         ...            \n",
       "24        0.0         0.000000         1.222222         ...            \n",
       "25        0.0         3.000000         3.000000         ...            \n",
       "26        3.0        11.000000         1.833333         ...            \n",
       "27        5.0         1.333333        20.000000         ...            \n",
       "28        4.0         1.962963         2.409091         ...            \n",
       "29        2.0         4.200000         2.333333         ...            \n",
       "...       ...              ...              ...         ...            \n",
       "30512     0.0         0.000000         0.000000         ...            \n",
       "30513     0.0         1.000000         0.000000         ...            \n",
       "30514     0.0         0.000000         0.000000         ...            \n",
       "30515     0.0         1.000000         0.000000         ...            \n",
       "30516     0.0         0.000000         0.000000         ...            \n",
       "30517     1.0         0.000000         0.000000         ...            \n",
       "30518     0.0         0.000000         0.000000         ...            \n",
       "30519     0.0         0.000000         1.000000         ...            \n",
       "30520     0.0         0.000000         0.000000         ...            \n",
       "30521     0.0         1.000000         0.000000         ...            \n",
       "30522     0.0         0.000000         1.000000         ...            \n",
       "30523     0.0         0.000000         1.000000         ...            \n",
       "30524     0.0         0.000000         1.000000         ...            \n",
       "30525     0.0         0.000000         1.000000         ...            \n",
       "30526     0.0         0.000000         1.000000         ...            \n",
       "30527     0.0         0.000000         1.000000         ...            \n",
       "30528     0.0         0.000000         1.000000         ...            \n",
       "30529     0.0         0.000000         1.000000         ...            \n",
       "30530     1.0         0.000000         0.000000         ...            \n",
       "30531     0.0         1.000000         0.000000         ...            \n",
       "30532     0.0         1.000000         0.000000         ...            \n",
       "30533     0.0         0.000000         0.000000         ...            \n",
       "30534     0.0         1.000000         0.000000         ...            \n",
       "30535     0.0         1.000000         0.000000         ...            \n",
       "30536     0.0         0.000000         1.000000         ...            \n",
       "30537     0.0         1.000000         0.000000         ...            \n",
       "30538     0.0         1.000000         0.000000         ...            \n",
       "30539     0.0         1.000000         0.000000         ...            \n",
       "30540     0.0         1.000000         0.000000         ...            \n",
       "30541     0.0         1.000000         0.000000         ...            \n",
       "\n",
       "       trans_type2_mean_0  trans_type2_mean_102  trans_type2_mean_103  \\\n",
       "0            0.000000e+00          12223.000000              0.000000   \n",
       "1            0.000000e+00          12331.750000              0.000000   \n",
       "2            0.000000e+00           8529.818182              0.000000   \n",
       "3            5.197350e+04              0.000000              0.000000   \n",
       "4            0.000000e+00           3429.500000              0.000000   \n",
       "5            6.672400e+03           9069.750000              0.000000   \n",
       "6            0.000000e+00         316727.200000              0.000000   \n",
       "7            0.000000e+00              0.000000              0.000000   \n",
       "8            8.179500e+03          12182.000000              0.000000   \n",
       "9            0.000000e+00           2236.500000              0.000000   \n",
       "10           0.000000e+00          81648.000000              0.000000   \n",
       "11           0.000000e+00           3769.000000              0.000000   \n",
       "12           0.000000e+00           8254.500000              0.000000   \n",
       "13           1.459000e+03         267336.950000           3490.750000   \n",
       "14           0.000000e+00           1472.333333              0.000000   \n",
       "15           0.000000e+00           6845.380952           3941.750000   \n",
       "16           0.000000e+00          54465.000000              0.000000   \n",
       "17           0.000000e+00           1730.000000              0.000000   \n",
       "18           0.000000e+00           3788.857143              0.000000   \n",
       "19           1.021160e+04         182532.500000           2124.018519   \n",
       "20           0.000000e+00          24510.142857           1141.333333   \n",
       "21           0.000000e+00         675875.770833              0.000000   \n",
       "22           0.000000e+00           1692.489362              0.000000   \n",
       "23           1.459000e+03           8267.400000           1812.000000   \n",
       "24           1.020233e+04              0.000000              0.000000   \n",
       "25           3.562850e+04              0.000000              0.000000   \n",
       "26           0.000000e+00          10099.250000              0.000000   \n",
       "27           8.160110e+05          62734.954545              0.000000   \n",
       "28           0.000000e+00          62270.738095           2566.000000   \n",
       "29           1.137073e+06         819127.875000              0.000000   \n",
       "...                   ...                   ...                   ...   \n",
       "30512        4.992000e+03              0.000000              0.000000   \n",
       "30513        0.000000e+00              0.000000              0.000000   \n",
       "30514        0.000000e+00              0.000000              0.000000   \n",
       "30515        7.847000e+03              0.000000              0.000000   \n",
       "30516        2.682000e+03              0.000000              0.000000   \n",
       "30517        7.684000e+03              0.000000              0.000000   \n",
       "30518        4.449000e+03              0.000000              0.000000   \n",
       "30519        4.992000e+03              0.000000              0.000000   \n",
       "30520        4.992000e+03              0.000000              0.000000   \n",
       "30521        2.682000e+03              0.000000              0.000000   \n",
       "30522        0.000000e+00              0.000000              0.000000   \n",
       "30523        0.000000e+00              0.000000              0.000000   \n",
       "30524        0.000000e+00              0.000000              0.000000   \n",
       "30525        0.000000e+00              0.000000              0.000000   \n",
       "30526        0.000000e+00              0.000000              0.000000   \n",
       "30527        4.992000e+03              0.000000              0.000000   \n",
       "30528        0.000000e+00              0.000000              0.000000   \n",
       "30529        0.000000e+00              0.000000              0.000000   \n",
       "30530        4.449000e+03              0.000000              0.000000   \n",
       "30531        2.671000e+03              0.000000              0.000000   \n",
       "30532        0.000000e+00              0.000000              0.000000   \n",
       "30533        2.671000e+03              0.000000              0.000000   \n",
       "30534        0.000000e+00              0.000000              0.000000   \n",
       "30535        2.682000e+03              0.000000              0.000000   \n",
       "30536        0.000000e+00              0.000000              0.000000   \n",
       "30537        5.242000e+03              0.000000              0.000000   \n",
       "30538        0.000000e+00              0.000000              0.000000   \n",
       "30539        2.682000e+03              0.000000              0.000000   \n",
       "30540        4.992000e+03              0.000000              0.000000   \n",
       "30541        1.397400e+04              0.000000              0.000000   \n",
       "\n",
       "       trans_type2_mean_104  trans_type2_mean_105  trans_type2_max_0  \\\n",
       "0                  0.000000           4538.357143                0.0   \n",
       "1                  0.000000           1527.666667                0.0   \n",
       "2                  0.000000            780.937209                0.0   \n",
       "3                  0.000000              0.000000           112699.0   \n",
       "4                  0.000000           3198.500000                0.0   \n",
       "5                  0.000000              0.000000            21302.0   \n",
       "6                  0.000000          69381.000000                0.0   \n",
       "7                  0.000000           1431.400000                0.0   \n",
       "8                  0.000000              0.000000            24020.0   \n",
       "9                  0.000000          13691.000000                0.0   \n",
       "10                 0.000000           2359.250000                0.0   \n",
       "11                 0.000000           3293.500000                0.0   \n",
       "12                 0.000000           3395.250000                0.0   \n",
       "13              1459.000000           2259.000000             1459.0   \n",
       "14                 0.000000          13691.000000                0.0   \n",
       "15                 0.000000           4177.000000                0.0   \n",
       "16                 0.000000           4947.000000                0.0   \n",
       "17                 0.000000           1594.500000                0.0   \n",
       "18                 0.000000           6895.000000                0.0   \n",
       "19              6672.480000           2138.250000            37068.0   \n",
       "20               107.599284            185.900000                0.0   \n",
       "21            917519.375000          77959.000000                0.0   \n",
       "22                 0.000000           1335.090909                0.0   \n",
       "23                 0.000000           1096.333333             1459.0   \n",
       "24              1911.777778           3578.900000            24931.0   \n",
       "25                 0.000000              0.000000            77842.0   \n",
       "26                 0.000000           8163.666667                0.0   \n",
       "27              5053.545455           3724.000000          1088266.0   \n",
       "28                 0.000000           3905.000000                0.0   \n",
       "29                 0.000000              0.000000          2718381.0   \n",
       "...                     ...                   ...                ...   \n",
       "30512              0.000000              0.000000             4992.0   \n",
       "30513              0.000000           2818.000000                0.0   \n",
       "30514              0.000000           6895.000000                0.0   \n",
       "30515              0.000000              0.000000             7847.0   \n",
       "30516              0.000000              0.000000             2682.0   \n",
       "30517              0.000000              0.000000             7684.0   \n",
       "30518              0.000000              0.000000             4449.0   \n",
       "30519              0.000000              0.000000             4992.0   \n",
       "30520              0.000000              0.000000             4992.0   \n",
       "30521              0.000000              0.000000             2682.0   \n",
       "30522              0.000000           4449.000000                0.0   \n",
       "30523              0.000000           2818.000000                0.0   \n",
       "30524              0.000000          10973.000000                0.0   \n",
       "30525              0.000000           2818.000000                0.0   \n",
       "30526              0.000000          15050.000000                0.0   \n",
       "30527              0.000000              0.000000             4992.0   \n",
       "30528              0.000000           2818.000000                0.0   \n",
       "30529              0.000000           2818.000000                0.0   \n",
       "30530              0.000000              0.000000             4449.0   \n",
       "30531              0.000000              0.000000             2671.0   \n",
       "30532              0.000000         271928.000000                0.0   \n",
       "30533              0.000000              0.000000             2671.0   \n",
       "30534              0.000000          13691.000000                0.0   \n",
       "30535              0.000000              0.000000             2682.0   \n",
       "30536              0.000000           2818.000000                0.0   \n",
       "30537              0.000000              0.000000             5242.0   \n",
       "30538              0.000000           1268.000000                0.0   \n",
       "30539              0.000000              0.000000             2682.0   \n",
       "30540              0.000000              0.000000             4992.0   \n",
       "30541              0.000000              0.000000            13974.0   \n",
       "\n",
       "       trans_type2_max_102  trans_type2_max_103  trans_type2_max_104  \\\n",
       "0                  27282.0                  0.0                  0.0   \n",
       "1                  27282.0                  0.0                  0.0   \n",
       "2                 228435.0                  0.0                  0.0   \n",
       "3                      0.0                  0.0                  0.0   \n",
       "4                   4177.0                  0.0                  0.0   \n",
       "5                  22389.0                  0.0                  0.0   \n",
       "6                 920273.0                  0.0                  0.0   \n",
       "7                      0.0                  0.0                  0.0   \n",
       "8                  24020.0                  0.0                  0.0   \n",
       "9                   8798.0                  0.0                  0.0   \n",
       "10                 81648.0                  0.0                  0.0   \n",
       "11                  3769.0                  0.0                  0.0   \n",
       "12                 13691.0                  0.0                  0.0   \n",
       "13               1359240.0              10212.0               1459.0   \n",
       "14                  2818.0                  0.0                  0.0   \n",
       "15                 27282.0               6080.0                  0.0   \n",
       "16                 54465.0                  0.0                  0.0   \n",
       "17                  1730.0                  0.0                  0.0   \n",
       "18                 13691.0                  0.0                  0.0   \n",
       "19               1359240.0              14884.0              38645.0   \n",
       "20                 80759.0               2818.0                113.0   \n",
       "21               5436663.0                  0.0            1359240.0   \n",
       "22                  3361.0                  0.0                  0.0   \n",
       "23                 26720.0               2818.0                  0.0   \n",
       "24                     0.0                  0.0               5536.0   \n",
       "25                     0.0                  0.0                  0.0   \n",
       "26                 56169.0                  0.0                  0.0   \n",
       "27                566622.0                  0.0              38155.0   \n",
       "28               1359240.0               5737.0                  0.0   \n",
       "29               1631069.0                  0.0                  0.0   \n",
       "...                    ...                  ...                  ...   \n",
       "30512                  0.0                  0.0                  0.0   \n",
       "30513                  0.0                  0.0                  0.0   \n",
       "30514                  0.0                  0.0                  0.0   \n",
       "30515                  0.0                  0.0                  0.0   \n",
       "30516                  0.0                  0.0                  0.0   \n",
       "30517                  0.0                  0.0                  0.0   \n",
       "30518                  0.0                  0.0                  0.0   \n",
       "30519                  0.0                  0.0                  0.0   \n",
       "30520                  0.0                  0.0                  0.0   \n",
       "30521                  0.0                  0.0                  0.0   \n",
       "30522                  0.0                  0.0                  0.0   \n",
       "30523                  0.0                  0.0                  0.0   \n",
       "30524                  0.0                  0.0                  0.0   \n",
       "30525                  0.0                  0.0                  0.0   \n",
       "30526                  0.0                  0.0                  0.0   \n",
       "30527                  0.0                  0.0                  0.0   \n",
       "30528                  0.0                  0.0                  0.0   \n",
       "30529                  0.0                  0.0                  0.0   \n",
       "30530                  0.0                  0.0                  0.0   \n",
       "30531                  0.0                  0.0                  0.0   \n",
       "30532                  0.0                  0.0                  0.0   \n",
       "30533                  0.0                  0.0                  0.0   \n",
       "30534                  0.0                  0.0                  0.0   \n",
       "30535                  0.0                  0.0                  0.0   \n",
       "30536                  0.0                  0.0                  0.0   \n",
       "30537                  0.0                  0.0                  0.0   \n",
       "30538                  0.0                  0.0                  0.0   \n",
       "30539                  0.0                  0.0                  0.0   \n",
       "30540                  0.0                  0.0                  0.0   \n",
       "30541                  0.0                  0.0                  0.0   \n",
       "\n",
       "       trans_type2_max_105  \n",
       "0                  30626.0  \n",
       "1                   3497.0  \n",
       "2                   2818.0  \n",
       "3                      0.0  \n",
       "4                   4449.0  \n",
       "5                      0.0  \n",
       "6                 135470.0  \n",
       "7                   1730.0  \n",
       "8                      0.0  \n",
       "9                  13691.0  \n",
       "10                  5957.0  \n",
       "11                  3769.0  \n",
       "12                  9021.0  \n",
       "13                  5944.0  \n",
       "14                 13691.0  \n",
       "15                  8078.0  \n",
       "16                  9200.0  \n",
       "17                  1730.0  \n",
       "18                 10728.0  \n",
       "19                  4280.0  \n",
       "20                  2818.0  \n",
       "21                136014.0  \n",
       "22                  1730.0  \n",
       "23                  1459.0  \n",
       "24                  4802.0  \n",
       "25                     0.0  \n",
       "26                 18312.0  \n",
       "27                  5536.0  \n",
       "28                  7893.0  \n",
       "29                     0.0  \n",
       "...                    ...  \n",
       "30512                  0.0  \n",
       "30513               2818.0  \n",
       "30514               6895.0  \n",
       "30515                  0.0  \n",
       "30516                  0.0  \n",
       "30517                  0.0  \n",
       "30518                  0.0  \n",
       "30519                  0.0  \n",
       "30520                  0.0  \n",
       "30521                  0.0  \n",
       "30522               4449.0  \n",
       "30523               2818.0  \n",
       "30524              10973.0  \n",
       "30525               2818.0  \n",
       "30526              15050.0  \n",
       "30527                  0.0  \n",
       "30528               2818.0  \n",
       "30529               2818.0  \n",
       "30530                  0.0  \n",
       "30531                  0.0  \n",
       "30532             271928.0  \n",
       "30533                  0.0  \n",
       "30534              13691.0  \n",
       "30535                  0.0  \n",
       "30536               2818.0  \n",
       "30537                  0.0  \n",
       "30538               1268.0  \n",
       "30539                  0.0  \n",
       "30540                  0.0  \n",
       "30541                  0.0  \n",
       "\n",
       "[30542 rows x 66 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_tst_feature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''交易金额和余额的统计信息'''\n",
    "train_amt = train_tsts.copy()\n",
    "train_amt = train_amt.groupby('UID')[['trans_amt', 'bal']].agg({'mean', 'min','max', 'sum'})\n",
    "train_amt.columns = [x[0]+'_'+x[1] for x in train_amt.columns.ravel()]\n",
    "train_amt.reset_index(inplace=True)\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_amt, on='UID', how='left')\n",
    "train_tst_feature['avg_trans_amt'] = train_tst_feature['trans_amt_sum'] / train_tst_feature['day_count']\n",
    "train_tst_feature['avg_bal_amt'] = train_tst_feature['bal_sum'] / train_tst_feature['day_count']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易资金类型amt_src1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_amt_src = train_tsts.copy()\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_amt_src.groupby('UID', as_index=False)['amt_src1'].count())\n",
    "train_amt_src.drop_duplicates(['UID', 'day', 'amt_src1'], inplace=True)\n",
    "train_amt_src = train_amt_src.groupby('UID', as_index=False)['amt_src1'].agg({'amt_src_count':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_amt_src, on='UID', how='left')\n",
    "train_tst_feature['avg_day_amt_src'] = train_tst_feature['amt_src_count'] / train_tst_feature['day_count']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 商家的信息统计方法与分析，merchant, code1, code2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''商家的个数，平均每天逛商家的个数'''\n",
    "train_merchant= train_tsts.copy()\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_merchant.groupby('UID', as_index=False)['merchant'].count())\n",
    "train_merchant.drop_duplicates(['UID', 'day', 'merchant'], inplace=True)\n",
    "train_merchant = train_merchant.groupby('UID', as_index=False)['merchant'].agg({'merchant_count':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_merchant, on='UID', how='left')\n",
    "train_tst_feature['avg_day_merchant'] = train_tst_feature['merchant_count'] / train_tst_feature['day_count']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp1 = train_tst.groupby(['UID', 'merchant'], as_index=False)['trans_amt'].agg({'sum', 'mean'}).add_prefix('merchant_').reset_index()\n",
    "temp1['trans_mer_number'] = temp1['merchant_sum'] / temp1['merchant_mean']\n",
    "temp1 = temp1.groupby('UID').agg({'mean','max', 'min','sum'})\n",
    "temp1.columns = [x[0]+'_'+x[1] for x in temp1.columns.ravel()]\n",
    "temp1.reset_index(inplace=True)\n",
    "train_tst_feature = pd.merge(train_tst_feature, temp1, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['UID', 'trans_count', 'day_count', 'avg_day_trans', 'time_0', 'time_1',\n",
       "       'time_2', 'time_3', 'avg_time0_trans', 'avg_time1_trans',\n",
       "       'avg_time2_trans', 'avg_time3_trans', 'day_shift_min', 'day_shift_mean',\n",
       "       'day_shift_max', 'channel_count', 'avg_day_diff_channel',\n",
       "       'channel_day_count', 'avg_day_channel', 'amt_channel_min_102',\n",
       "       'amt_channel_min_106', 'amt_channel_min_118', 'amt_channel_min_119',\n",
       "       'amt_channel_min_140', 'amt_channel_mean_102', 'amt_channel_mean_106',\n",
       "       'amt_channel_mean_118', 'amt_channel_mean_119', 'amt_channel_mean_140',\n",
       "       'amt_channel_max_102', 'amt_channel_max_106', 'amt_channel_max_118',\n",
       "       'amt_channel_max_119', 'amt_channel_max_140', 'mark_code_count',\n",
       "       'avg_day_mark_code', 'mark_code_count_diff', 'avg_day_diff_mark_code',\n",
       "       'market_type_min_0.0', 'market_type_min_1.0', 'market_type_min_2.0',\n",
       "       'market_type_mean_0.0', 'market_type_mean_1.0', 'market_type_mean_2.0',\n",
       "       'market_type_max_0.0', 'market_type_max_1.0', 'market_type_max_2.0',\n",
       "       'trans_type1_count', 'avg_day_trans_type1', 'trans_type2_count',\n",
       "       'avg_day_trans_type2', 'trans_type2_min_0', 'trans_type2_min_102',\n",
       "       'trans_type2_min_103', 'trans_type2_min_104', 'trans_type2_min_105',\n",
       "       'trans_type2_mean_0', 'trans_type2_mean_102', 'trans_type2_mean_103',\n",
       "       'trans_type2_mean_104', 'trans_type2_mean_105', 'trans_type2_max_0',\n",
       "       'trans_type2_max_102', 'trans_type2_max_103', 'trans_type2_max_104',\n",
       "       'trans_type2_max_105', 'trans_amt_min', 'trans_amt_mean',\n",
       "       'trans_amt_sum', 'trans_amt_max', 'bal_min', 'bal_mean', 'bal_sum',\n",
       "       'bal_max', 'avg_trans_amt', 'avg_bal_amt', 'amt_src1', 'amt_src_count',\n",
       "       'avg_day_amt_src', 'merchant', 'merchant_count', 'avg_day_merchant',\n",
       "       'merchant_mean_min', 'merchant_mean_mean', 'merchant_mean_sum',\n",
       "       'merchant_mean_max', 'merchant_sum_min', 'merchant_sum_mean',\n",
       "       'merchant_sum_sum', 'merchant_sum_max', 'trans_mer_number_min',\n",
       "       'trans_mer_number_mean', 'trans_mer_number_sum',\n",
       "       'trans_mer_number_max'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_tst_feature.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易表中地理位置信息分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>trans_count</th>\n",
       "      <th>day_count</th>\n",
       "      <th>avg_day_trans</th>\n",
       "      <th>time_0</th>\n",
       "      <th>time_1</th>\n",
       "      <th>time_2</th>\n",
       "      <th>time_3</th>\n",
       "      <th>avg_time0_trans</th>\n",
       "      <th>avg_time1_trans</th>\n",
       "      <th>...</th>\n",
       "      <th>merchant_mean_sum</th>\n",
       "      <th>merchant_mean_max</th>\n",
       "      <th>merchant_sum_min</th>\n",
       "      <th>merchant_sum_mean</th>\n",
       "      <th>merchant_sum_sum</th>\n",
       "      <th>merchant_sum_max</th>\n",
       "      <th>trans_mer_number_min</th>\n",
       "      <th>trans_mer_number_mean</th>\n",
       "      <th>trans_mer_number_sum</th>\n",
       "      <th>trans_mer_number_max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19092</td>\n",
       "      <td>19</td>\n",
       "      <td>9</td>\n",
       "      <td>2.111111</td>\n",
       "      <td>5.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.800000</td>\n",
       "      <td>2.111111</td>\n",
       "      <td>...</td>\n",
       "      <td>62517.500000</td>\n",
       "      <td>30626.0</td>\n",
       "      <td>2320</td>\n",
       "      <td>17807.428571</td>\n",
       "      <td>124652</td>\n",
       "      <td>57382</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>19.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13465</td>\n",
       "      <td>19</td>\n",
       "      <td>8</td>\n",
       "      <td>2.375000</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>2.375000</td>\n",
       "      <td>...</td>\n",
       "      <td>26191.166667</td>\n",
       "      <td>20486.5</td>\n",
       "      <td>8354</td>\n",
       "      <td>24080.666667</td>\n",
       "      <td>72242</td>\n",
       "      <td>40973</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.333333</td>\n",
       "      <td>19.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13713</td>\n",
       "      <td>485</td>\n",
       "      <td>17</td>\n",
       "      <td>28.529412</td>\n",
       "      <td>172.0</td>\n",
       "      <td>303.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.819767</td>\n",
       "      <td>1.600660</td>\n",
       "      <td>...</td>\n",
       "      <td>332257.778161</td>\n",
       "      <td>19242.6</td>\n",
       "      <td>643</td>\n",
       "      <td>2217.473829</td>\n",
       "      <td>804943</td>\n",
       "      <td>384852</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.336088</td>\n",
       "      <td>485.0</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22703</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>51973.500000</td>\n",
       "      <td>51973.5</td>\n",
       "      <td>311841</td>\n",
       "      <td>311841.000000</td>\n",
       "      <td>311841</td>\n",
       "      <td>311841</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17816</td>\n",
       "      <td>14</td>\n",
       "      <td>9</td>\n",
       "      <td>1.555556</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.500000</td>\n",
       "      <td>1.400000</td>\n",
       "      <td>...</td>\n",
       "      <td>19422.000000</td>\n",
       "      <td>3633.5</td>\n",
       "      <td>5908</td>\n",
       "      <td>7617.166667</td>\n",
       "      <td>45703</td>\n",
       "      <td>13718</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.333333</td>\n",
       "      <td>14.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 94 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     UID  trans_count  day_count  avg_day_trans  time_0  time_1  time_2  \\\n",
       "0  19092           19          9       2.111111     5.0     9.0     2.0   \n",
       "1  13465           19          8       2.375000     7.0     8.0     0.0   \n",
       "2  13713          485         17      28.529412   172.0   303.0    10.0   \n",
       "3  22703            6          2       3.000000     0.0     2.0     2.0   \n",
       "4  17816           14          9       1.555556     4.0    10.0     0.0   \n",
       "\n",
       "   time_3  avg_time0_trans  avg_time1_trans          ...           \\\n",
       "0     3.0         3.800000         2.111111          ...            \n",
       "1     4.0         2.714286         2.375000          ...            \n",
       "2     0.0         2.819767         1.600660          ...            \n",
       "3     2.0         0.000000         3.000000          ...            \n",
       "4     0.0         3.500000         1.400000          ...            \n",
       "\n",
       "   merchant_mean_sum  merchant_mean_max  merchant_sum_min  merchant_sum_mean  \\\n",
       "0       62517.500000            30626.0              2320       17807.428571   \n",
       "1       26191.166667            20486.5              8354       24080.666667   \n",
       "2      332257.778161            19242.6               643        2217.473829   \n",
       "3       51973.500000            51973.5            311841      311841.000000   \n",
       "4       19422.000000             3633.5              5908        7617.166667   \n",
       "\n",
       "   merchant_sum_sum  merchant_sum_max  trans_mer_number_min  \\\n",
       "0            124652             57382                   1.0   \n",
       "1             72242             40973                   2.0   \n",
       "2            804943            384852                   1.0   \n",
       "3            311841            311841                   6.0   \n",
       "4             45703             13718                   2.0   \n",
       "\n",
       "   trans_mer_number_mean  trans_mer_number_sum  trans_mer_number_max  \n",
       "0               2.714286                  19.0                   9.0  \n",
       "1               6.333333                  19.0                  15.0  \n",
       "2               1.336088                 485.0                  29.0  \n",
       "3               6.000000                   6.0                   6.0  \n",
       "4               2.333333                  14.0                   4.0  \n",
       "\n",
       "[5 rows x 94 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_tst_feature.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(264654, 27)\n"
     ]
    }
   ],
   "source": [
    "print(train_tst.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''平均每天变动的地理位置'''\n",
    "train_geo = train_tsts.copy()\n",
    "train_geo.geo_code = train_geo.geo_code.fillna(0)\n",
    "train_geo = train_geo.drop_duplicates(subset=['UID', 'day', 'geo_code'],keep='first')\n",
    "train_geo = train_geo.groupby(['UID', 'day'], as_index=False)['geo_code'].count()\n",
    "train_geo = train_geo.groupby('UID', as_index=False).agg({'day':'count', 'geo_code':'sum'})\n",
    "train_geo['avg_day_geo_tst'] = train_geo['geo_code'] / train_geo['day']\n",
    "train_geo.drop(['day'], axis=1, inplace=True)\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_geo, on='UID', how='left')\n",
    "\n",
    "'''平均多少次交易变动一次地理位置'''\n",
    "train_geo_trans = train_tsts.copy()\n",
    "train_geo_trans['geo_code'] = train_geo_trans['geo_code'].fillna(0)\n",
    "train_geo_trans.drop_duplicates(['UID', 'geo_code'], inplace=True)\n",
    "train_geo_trans = train_geo_trans.groupby('UID', as_index=False)['geo_code'].agg({'avg_trans_geo_count':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_geo_trans, on='UID', how='left')\n",
    "train_tst_feature['avg_trans_geo'] = train_tst_feature['trans_count'] / train_tst_feature['avg_trans_geo_count']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 用户交易的帐号信息acc_id1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''交易帐号的个数以及帐号变动的次数'''\n",
    "train_acc_id = train_tsts.copy()\n",
    "train_acc_id['acc_id1'] = train_acc_id['acc_id1'].fillna(0)\n",
    "train_acc_id.drop_duplicates(['UID', 'acc_id1'], inplace=True)\n",
    "train_acc_id = train_acc_id.groupby('UID', as_index=False)['acc_id1'].agg({'acc_id1_count':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_acc_id, on='UID', how='left')\n",
    "train_tst_feature['avg_trans_acc_id1'] = train_tst_feature['trans_count'] / train_tst_feature['acc_id1_count']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易表的设备特征提取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''一个用户用了几个设置进行交易, 以及没多少次交易换设备，以及平均每天设备个数'''\n",
    "train_device = train_tsts.copy()\n",
    "train_device.device2 = train_device.device2.fillna(0)\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_device.groupby('UID', as_index=False)['device2'].agg({'device_tst':'count'}))\n",
    "train_device.drop_duplicates(['UID','day','device2'], inplace=True)\n",
    "train_device = train_device.groupby(['UID'], as_index=False)['device2'].agg({'device_count_tst':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_device, on='UID', how='left')\n",
    "train_tst_feature['avg_trans_device'] =  train_tst_feature['trans_count'] / train_tst_feature['device_count_tst']\n",
    "train_tst_feature['avg_day_decive_tst'] = train_tst_feature['device_tst'] / train_tst_feature['day_count']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# mac1地址的统计分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''统计用户mac地址的个数'''\n",
    "train_mac = train_tsts.copy()\n",
    "train_mac.mac1 = train_mac.mac1.fillna(0)\n",
    "train_mac = train_mac.groupby(['UID', 'mac1'], as_index=False)['day'].agg({'mac_count_tst':'count'})\n",
    "train_mac['ratio'] = np.where(train_mac.mac1 == 0, 0, 1)\n",
    "train_mac_count = train_mac.groupby('UID', as_index=False)['mac1'].count()\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_mac_count, on='UID', how='left')\n",
    "\n",
    "'''统计mac地址的缺失率'''\n",
    "train_mac_loss = train_tsts.groupby('UID', as_index=False).count()\n",
    "train_mac_loss['mac_loss_tst'] = train_mac_loss.mac1 / train_mac_loss.day\n",
    "train_mac_loss = train_mac_loss[['UID', 'mac_loss_tst']]\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_mac_loss, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易表中交易是否为苹果手机"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_apple = train_tsts.copy()\n",
    "train_apple.device_code3 = train_apple.device_code3.fillna(0)\n",
    "train_apple.device_code1 = train_apple.device_code1.fillna(0)\n",
    "\n",
    "train_apple.device_code3= train_apple.device_code3.apply(lambda x: x if x == 0 else 1)\n",
    "train_apple.device_code1 = train_apple.device_code1.apply(lambda x: x if x == 0 else 1)\n",
    "\n",
    "train_apple = train_apple.groupby('UID', as_index=False)[['device_code1', 'device_code3']].sum()\n",
    "train_apple['is_apple_tst'] = np.where(train_apple.device_code1 > train_apple.device_code3, 0,1)\n",
    "train_apple['is_apple_an_tst'] = np.where((train_apple.device_code1!=0)&(train_apple.device_code3!=0),1,0)\n",
    "train_apple.drop(['device_code1', 'device_code3'], axis=1, inplace=True)\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_apple, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ip地址信息的相关统计分析ip1,ip1_sub"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "'''平均每天换多少个ip'''\n",
    "train_ip = train_tsts.copy()\n",
    "train_ip['ip1'] = train_ip['ip1'].fillna(0)\n",
    "train_ip.drop_duplicates(['UID', 'day', 'ip1'], inplace=True)\n",
    "train_ip = train_ip.groupby('UID', as_index=False)['ip1'].agg({'ip1_count_tst':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_ip, on='UID', how='left')\n",
    "train_tst_feature['avg_day_ip_tst'] = train_tst_feature['ip1_count_tst'] / train_tst_feature['day_count']\n",
    "\n",
    "\n",
    "'''每多少次交易换一次ip'''\n",
    "train_ip_trans = train_tsts.copy()\n",
    "train_ip_trans['ip1'] = train_ip_trans['ip1'].fillna(0)\n",
    "train_ip_trans = train_ip_trans.groupby('UID', as_index=False)['ip1'].agg({'ip1_count_unique':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_ip_trans, on='UID', how='left')\n",
    "train_tst_feature['avg_trans_ip1'] = train_tst_feature['trans_count'] / train_tst_feature['ip1_count_unique']\n",
    "\n",
    "\n",
    "'''交易ip的缺失率'''\n",
    "train_ip_loss = train_tsts.groupby('UID', as_index=False).count()\n",
    "train_ip_loss['ip_loss_tst'] = 1 - train_ip_loss.ip1 / train_ip_loss.day\n",
    "train_ip_loss = train_ip_loss[['UID', 'ip_loss_tst']]\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_ip_loss, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 特征的保存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "train_tst_feature.to_csv('./train_tst_feature.csv', index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
